package com.zj.osm.utils;

import android.content.Context;
import android.util.Log;

import com.bin.david.form.annotation.SmartColumn;
import com.zj.osm.bean.BomBean;
import com.zj.osm.bean.DianLjinTable;
import com.zj.osm.bean.HwxTable;
import com.zj.osm.bean.PoleBean;
import com.zj.osm.bean.ProgramTypeTable;
import com.zj.osm.bean.RoadSrcTable;
import com.zj.utils.utils.Utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


/**
 * excel工具类
 *
 * @author jiang zhu on 2019/4/14
 */
public class ExcelUtil {
    private final static String UTF8_ENCODING = "UTF-8";
    private static WritableCellFormat arial10format = null;

    public static void format() {
        try {
            WritableFont arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
            arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
            WritableCellFormat arial14format = new WritableCellFormat(arial14font);
            arial14format.setAlignment(jxl.format.Alignment.CENTRE);
            arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
            WritableFont arial10font = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD);
            arial10format = new WritableCellFormat(arial10font);
            arial10format.setAlignment(jxl.format.Alignment.CENTRE);
            arial10format.setVerticalAlignment(VerticalAlignment.CENTRE);
            arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            arial10format.setBackground(Colour.WHITE);
            WritableFont arial12font = new WritableFont(WritableFont.ARIAL, 12);
            WritableCellFormat arial12format = new WritableCellFormat(arial12font);
            arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    public static void initExcel(String name, String fileName, String[] colName) {
        format();
        WritableWorkbook workbook = null;
        try {
            File file = new File(fileName);
            if (!file.exists()) {
                file.delete();
                file.createNewFile();
            }
            workbook = Workbook.createWorkbook(file);
            WritableSheet sheet = workbook.createSheet("物料清单", 0);
            WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE); // 定义格式 字体 下划线 斜体 粗体 颜色
            WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
            wcf.setAlignment(jxl.format.Alignment.CENTRE);
            sheet.addCell(new Label(0, 0, name, wcf));// 合并单元格
            sheet.setRowView(0, 350);
            sheet.mergeCells(0, 0, 5, 0); // 合并单元格
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 1, colName[col], arial10format));
            }
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    public static void initExcelPole(String name, String fileName, String[] colName) {
        format();
        WritableWorkbook workbook = null;
        try {
            File file = new File(fileName);
            if (!file.exists()) {
                file.delete();
                file.createNewFile();
            }
            workbook = Workbook.createWorkbook(file);
            WritableSheet sheet = workbook.createSheet("廊道现状问题清册", 0);
            WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE); // 定义格式 字体 下划线 斜体 粗体 颜色
            WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
            wcf.setAlignment(jxl.format.Alignment.CENTRE);
            //sheet.addCell(new Label(0, 0, name, wcf));// 合并单元格
//            sheet.setRowView(0, 350);
//            sheet.mergeCells(0, 0, 6, 0); // 合并单元格
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 0, colName[col], arial10format));
            }
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    public static void initExcelPole3(String name, String fileName, String[] colName, String[] colName1, String[] colName2) {
        format();
        WritableWorkbook workbook = null;
        try {
            File file = new File(fileName);
            if (!file.exists()) {
                file.delete();
                file.createNewFile();
            }
            workbook = Workbook.createWorkbook(file);
            WritableSheet sheet = workbook.createSheet("1-1 现状廊道资源台账", 0);
            WritableSheet sheet_1 = workbook.createSheet("1-2 现状电缆井台账", 1);
            WritableSheet sheet_2 = workbook.createSheet("1-3 现状环网箱台账", 2);

            WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE); // 定义格式 字体 下划线 斜体 粗体 颜色
            WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
            wcf.setAlignment(jxl.format.Alignment.CENTRE);
            //sheet.addCell(new Label(0, 0, name, wcf));// 合并单元格
//            sheet.setRowView(0, 350);
            Utils.INSTANCE.mergeCells(sheet, colName.length, 0, 1);// 合并单元格
            for (int col = 0; col < colName.length; col++) {
                sheet.addCell(new Label(col, 0, colName[col], arial10format));
            }

            //sheet_1.addCell(new Label(0, 0, name, wcf));// 合并单元格
//            sheet_1.setRowView(0, 350);
            Utils.INSTANCE.mergeCells(sheet_1, colName1.length, 0, 1);// 合并单元格
            for (int col = 0; col < colName1.length; col++) {
                sheet_1.addCell(new Label(col, 0, colName1[col], arial10format));
            }

            //sheet_2.addCell(new Label(0, 0, name, wcf));// 合并单元格
//            sheet_2.setRowView(0, 350);
            Utils.INSTANCE.mergeCells(sheet_2, colName2.length, 0, 1);// 合并单元格
            for (int col = 0; col < colName2.length; col++) {
                sheet_2.addCell(new Label(col, 0, colName2[col], arial10format));
            }
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    private static void mergeCell(WritableSheet sheet, int colNum) {
        for (int i = 0; i < colNum; i++) {
            try {
                sheet.mergeCells(i, 0, i, 1);
            } catch (WriteException e) {
                e.printStackTrace();
            }
        }
    }

    @SuppressWarnings("unchecked")
    public static <T> File writeObjListToExcel(List<T> objList, String fileName, Context c) {
        File file = null;
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            file = new File(fileName);
            if (!file.exists()) {
                try {
                    file.delete();
                    file.createNewFile();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(file);
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName), workbook);

                WritableSheet sheet = null;
                try {
                    sheet = writebook.getSheet(0);
                } catch (IndexOutOfBoundsException e) {
                    e.printStackTrace();
                }

                for (int j = 1; j < objList.size() + 1; j++) {
                    /*ArrayList<String> list=(ArrayList<String>) objList.get(j);
                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j+1, list.get(i), arial12format));
                    }*/
                    BomBean projectBean = (BomBean) objList.get(j - 1);
                    List<String> list = new ArrayList<>();
                    list.add(projectBean.getMaterialsProjectID());
                    list.add(projectBean.getMaterialCodeERP());
                    list.add(projectBean.getMaterialName());
                    list.add(projectBean.getSpec());
                    list.add(projectBean.getERPUnit());
                    list.add(projectBean.getNum());
                    list.add(projectBean.getTechnicalProtocol());
                    //list.add(projectBean.getBomType());

                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j + 1, list.get(i), arial10format));
                        /*if (list.get(i).length() <= 4) {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 8);
                        } else {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 5);
                        }*/
                    }


                    //设置行高
                    sheet.setRowView(j, 270);

                }
                //设置列宽
                sheet.setColumnView(0, 11);
                sheet.setColumnView(1, 14);
                sheet.setColumnView(2, 20);
                sheet.setColumnView(3, 30);
                sheet.setColumnView(4, 8);
                sheet.setColumnView(5, 8);
                sheet.setColumnView(6, 24);
                sheet.setRowView(0, 270);
                writebook.write();
                //Toast.makeText(c, "保存成功", Toast.LENGTH_SHORT).show();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return file;
    }

    @SuppressWarnings("unchecked")
    public static <T> File writeObjListToExcelToPel(List<T> objList, String fileName, Context c) {
        File file = null;
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            file = new File(fileName);
            if (!file.exists()) {
                try {
                    file.delete();
                    file.createNewFile();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(file);
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName), workbook);

                WritableSheet sheet = null;
                try {
                    sheet = writebook.getSheet(0);
                } catch (IndexOutOfBoundsException e) {
                    e.printStackTrace();
                }

                for (int j = 1; j < objList.size() + 1; j++) {
                    /*ArrayList<String> list=(ArrayList<String>) objList.get(j);
                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j+1, list.get(i), arial12format));
                    }*/
                    PoleBean projectBean = (PoleBean) objList.get(j - 1);
                    List<String> list = new ArrayList<>();
                    list.add(projectBean.getEquipCode());
                    list.add(projectBean.getEquipCategory());
                    list.add(projectBean.getEquipType());
                    list.add(projectBean.getEquipEndLat());
                    list.add(projectBean.getEquipEndLong());
                    list.add(projectBean.getEquipEndHeight());
                    list.add(projectBean.getEquipEndNote());
                    /*list.add(projectBean.getEquipStartX());
                    list.add(projectBean.getEquipStartY());
                    list.add(projectBean.getEquipEndX());
                    list.add(projectBean.getEquipEndY());*/
                    //list.add(projectBean.getBomType());

                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j, list.get(i), arial10format));
                        /*if (list.get(i).length() <= 4) {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 8);
                        } else {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 5);
                        }*/
                    }


                    //设置行高
                    sheet.setRowView(j, 270);

                }
                //设置列宽
                sheet.setColumnView(0, 11);
                sheet.setColumnView(1, 14);
                sheet.setColumnView(2, 50);
                sheet.setColumnView(3, 30);
                sheet.setColumnView(4, 30);
                sheet.setColumnView(5, 30);
                sheet.setColumnView(6, 30);
                sheet.setRowView(0, 270);
                writebook.write();
                //Toast.makeText(c, "保存成功", Toast.LENGTH_SHORT).show();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return file;
    }

    //导出项目狼道资源等问题列表
    @SuppressWarnings("unchecked")
    public static <T extends Object> File write3ObjListToExcelToProgram(List<RoadSrcTable> objList, List<DianLjinTable> objList_1, List<HwxTable> objList_2, String fileName, Context c) {
        File file = null;
//        if (objList != null && objList.size() > 0) {
        WritableWorkbook writebook = null;
        InputStream in = null;
        file = new File(fileName);
        if (!file.exists()) {
            try {
                file.delete();
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        try {
            WorkbookSettings setEncode = new WorkbookSettings();
            setEncode.setEncoding(UTF8_ENCODING);
            in = new FileInputStream(file);
            Workbook workbook = Workbook.getWorkbook(in);
            writebook = Workbook.createWorkbook(new File(fileName), workbook);

            WritableSheet sheet = null;
            WritableSheet sheet_1 = null;
            WritableSheet sheet_2 = null;

            try {
                sheet = writebook.getSheet(0);
                sheet_1 = writebook.getSheet(1);
                sheet_2 = writebook.getSheet(2);
            } catch (IndexOutOfBoundsException e) {
                e.printStackTrace();
            }

            for (int j = 0; j < objList.size(); j++) {
                RoadSrcTable projectBean = (RoadSrcTable) objList.get(j);
                List<String> list = new ArrayList<>();
                list.add(projectBean.getID() + "");
                list.add(projectBean.getOwnGrid());
                list.add(projectBean.getRoadName());
                list.add(removeNull(projectBean.getRoadZName()));
                list.add(projectBean.getLayingMethod());
                list.add(projectBean.getRoadStarName());
                list.add(projectBean.getRoadEndName());
                list.add(projectBean.getDlgGz());
                list.add(projectBean.getTdGG());
                list.add(projectBean.getDlpgGG());
                list.add(projectBean.getAlreadyOccupy());
                list.add(projectBean.getRoadNumber());
                list.add(projectBean.getRoadRatio());
                list.add(projectBean.getRoadLinePush());
                for (int i = 0; i < list.size(); i++) {
                    sheet.addCell(new Label(i, j + 2, list.get(i), arial10format));
                }


                //设置行高
                sheet.setRowView(j, 270);

            }
            //第二个表格的设置
            for (int j = 0; j < objList_1.size(); j++) {
                DianLjinTable projectBean = objList_1.get(j);
                List<String> list = new ArrayList<>();
                list.add(projectBean.getID() + "");
                list.add(projectBean.getRoadName());
                list.add(removeNull(projectBean.getOwnGridName()));
                list.add(projectBean.getDljNumber());
                list.add(projectBean.getDljProperty());
                list.add(projectBean.getDljcc());
                list.add(projectBean.getLatitude());
                list.add(projectBean.getLongitude());
                list.add(projectBean.getX());
                list.add(projectBean.getY());
                list.add(projectBean.getZ());
                list.add(projectBean.getNote());
//                    list.add(projectBean.getRoadLocation());
//                    list.add(projectBean.getDljAddress());
//                    String rsState = projectBean.getResourceStatus();
//                    list.add(rsState);
//                    if (rsState.contains("规划态")){
//                        list.add(projectBean.getPlanYear());
//                    }else {
//                        list.add("");
//                    }
                for (int i = 0; i < list.size(); i++) {
                    sheet_1.addCell(new Label(i, j + 2, list.get(i), arial10format));
                }


                //设置行高
                sheet_1.setRowView(j, 270);

            }
            //第三个表格的设置
            for (int j = 0; j < objList_2.size(); j++) {
                HwxTable projectBean = (HwxTable) objList_2.get(j);
                List<String> list = new ArrayList<>();
                list.add(projectBean.getID() + "");
                list.add(projectBean.getRoadName());
                list.add(removeNull(projectBean.getOwnGridName()));
                list.add(projectBean.getRoadZName());
                list.add(projectBean.getHwxNumber());
                list.add(projectBean.getHwxType());
                list.add(projectBean.getHwxNature());
                list.add(projectBean.getJgNumber());
                list.add(projectBean.getLatitude());
                list.add(projectBean.getLongitude());
                list.add(projectBean.getX());
                list.add(projectBean.getY());
                list.add(projectBean.getZ());
                list.add(projectBean.getNoPlaque());
                list.add(projectBean.getPlaqueErro());
                list.add(projectBean.getWjjDlPlaque());
                list.add(projectBean.getRoadLocation());
                list.add(projectBean.getNote());
                for (int i = 0; i < list.size(); i++) {
                    sheet_2.addCell(new Label(i, j + 2, list.get(i), arial10format));
                }


                //设置行高
                sheet_2.setRowView(j, 270);

            }

            //设置列宽
            sheet.setColumnView(0, 11);
            sheet.setColumnView(1, 14);
            sheet.setColumnView(2, 50);
            sheet.setColumnView(3, 30);
            sheet.setColumnView(4, 30);
            sheet.setColumnView(5, 30);
            sheet.setColumnView(6, 30);
            sheet.setRowView(0, 270);
            writebook.write();
            //Toast.makeText(c, "保存成功", Toast.LENGTH_SHORT).show();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (writebook != null) {
                try {
                    writebook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
//        }
        return file;
    }

    private static String removeNull(String value) {
        if (null == value) {
            value = "";
        }
        return value;
    }

    //导出项目狼道资源等问题列表
    @SuppressWarnings("unchecked")
    public static <T> File writeObjListToExcelToProgram(List<T> objList, String fileName, Context c) {
        File file = null;
        if (objList != null && objList.size() > 0) {
            WritableWorkbook writebook = null;
            InputStream in = null;
            file = new File(fileName);
            if (!file.exists()) {
                try {
                    file.delete();
                    file.createNewFile();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(file);
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(fileName), workbook);

                WritableSheet sheet = null;
                try {
                    sheet = writebook.getSheet(0);
                } catch (IndexOutOfBoundsException e) {
                    e.printStackTrace();
                }

                for (int j = 1; j < objList.size() + 1; j++) {
                    ProgramTypeTable projectBean = (ProgramTypeTable) objList.get(j - 1);
                    List<String> list = new ArrayList<>();
                    list.add(projectBean.getID() + "");
                    list.add(projectBean.getOwnGrid());
                    list.add(projectBean.getCableWellNumber());
                    list.add(projectBean.getProgram1());
                    list.add(projectBean.getProgram2());
                    list.add(projectBean.getProgram3());
                    list.add(projectBean.getProgram4());
                    list.add(projectBean.getProgram5());
                    list.add(projectBean.getProgram6());
                    list.add(projectBean.getProgram7());
                    list.add(projectBean.getProgram8());
                    list.add(projectBean.getProgram9());
                    list.add(projectBean.getProgram10());
                    list.add(projectBean.getProgram11());
                    list.add(projectBean.getProgram12());
                    list.add(projectBean.getProgram13());
                    list.add(projectBean.getProgram14());
                    list.add(projectBean.getProgram15());
                    for (int i = 0; i < list.size(); i++) {
                        sheet.addCell(new Label(i, j, list.get(i), arial10format));
                        /*if (list.get(i).length() <= 4) {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 8);
                        } else {
                            //设置列宽
                            sheet.setColumnView(i, list.get(i).length() + 5);
                        }*/
                    }


                    //设置行高
                    sheet.setRowView(j, 270);

                }
                //设置列宽
                sheet.setColumnView(0, 11);
                sheet.setColumnView(1, 14);
                sheet.setColumnView(2, 50);
                sheet.setColumnView(3, 30);
                sheet.setColumnView(4, 30);
                sheet.setColumnView(5, 30);
                sheet.setColumnView(6, 30);
                sheet.setRowView(0, 270);
                writebook.write();
                //Toast.makeText(c, "保存成功", Toast.LENGTH_SHORT).show();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return file;
    }

    public static Object getValueByRef(Class cls, String fieldName) {
        Object value = null;
        fieldName = fieldName.replaceFirst(fieldName.substring(0, 1), fieldName.substring(0, 1).toUpperCase());
        String getMethodName = "get" + fieldName;
        try {
            Method method = cls.getMethod(getMethodName);
            value = method.invoke(cls);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }
}

